package zy.dao.base.emp.impl;

import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import zy.dao.BaseDaoImpl;
import zy.dao.base.emp.EmpGroupDAO;
import zy.entity.base.emp.T_Base_Emp;
import zy.entity.base.emp.T_Base_EmpGroup;
import zy.entity.base.emp.T_Base_EmpGroupList;
import zy.util.CommonUtil;
import zy.util.StringUtil;

@Repository
public class EmpGroupDAOImpl extends BaseDaoImpl implements EmpGroupDAO{
	
	@Override
	public List<T_Base_EmpGroup> list(Map<String, Object> params) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT eg_id,eg_code,eg_name,eg_remark,eg_shop_code,companyid");
		sql.append(" FROM t_base_empgroup t");
		sql.append(" WHERE 1 = 1");
		sql.append(" AND eg_shop_code = :shop_code");
		if (StringUtil.isNotEmpty(params.get("searchContent"))) {
			sql.append(" AND (INSTR(eg_code,:searchContent)>0 OR INSTR(eg_name,:searchContent)>0)");
		}
		sql.append(" AND t.companyid=:companyid");
		if (StringUtil.isNotEmpty(params.get(CommonUtil.SIDX))) {
			sql.append(" ORDER BY ").append(params.get(CommonUtil.SIDX)).append(" ").append(params.get(CommonUtil.SORD));
		}else {
			sql.append(" ORDER BY eg_code ASC");
		}
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Base_EmpGroup.class));
	}
	
	@Override
	public T_Base_EmpGroup load(Integer eg_id) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT eg_id,eg_code,eg_name,eg_remark,eg_shop_code,companyid");
		sql.append(" FROM t_base_empgroup t");
		sql.append(" WHERE 1 = 1");
		sql.append(" AND eg_id = :eg_id");
		sql.append(" LIMIT 1");
		try {
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), new MapSqlParameterSource().addValue("eg_id", eg_id),
					new BeanPropertyRowMapper<>(T_Base_EmpGroup.class));
		} catch (Exception e) {
			return null;
		}
	}
	
	@Override
	public List<T_Base_EmpGroupList> listDetails(Map<String, Object> params) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT egl_id,egl_eg_code,egl_em_code,companyid,");
		sql.append(" (SELECT em_name FROM t_base_emp em WHERE em_code = egl_em_code AND em.companyid = t.companyid LIMIT 1) AS em_name");
		sql.append(" FROM t_base_empgrouplist t");
		sql.append(" WHERE 1 = 1");
		sql.append(" AND egl_eg_code = :eg_code");
		sql.append(" AND t.companyid=:companyid");
		if (StringUtil.isNotEmpty(params.get(CommonUtil.SIDX))) {
			sql.append(" ORDER BY ").append(params.get(CommonUtil.SIDX)).append(" ").append(params.get(CommonUtil.SORD));
		}else {
			sql.append(" ORDER BY egl_eg_code ASC");
		}
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Base_EmpGroupList.class));
	}
	
	@Override
	public List<T_Base_Emp> listEmps(Map<String, Object> params) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT em_id,em_code,em_name,em_type,IF(egl_id IS NULL,0,1) AS em_state");
		sql.append(" FROM t_base_emp t");
		sql.append(" JOIN t_base_shop sp ON sp.sp_code = t.em_shop_code AND sp.companyid = t.companyid");
		sql.append(" LEFT JOIN t_base_empgrouplist egl ON egl_em_code = em_code AND egl.companyid = t.companyid AND egl_eg_code = :eg_code");
		sql.append(" WHERE 1 = 1");
        sql.append(" AND (em_login_shop=:shop_code OR em_shop_code=:shop_code)");
		sql.append(" AND t.companyid=:companyid");
		sql.append(" ORDER BY em_code ASC");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Base_Emp.class));
	}
	
	@Override
	public void save(T_Base_EmpGroup empGroup, List<T_Base_EmpGroupList> groupLists) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT f_three_code(max(eg_code+0)) FROM t_base_empgroup ");
		sql.append(" WHERE 1=1");
		sql.append(" AND companyid=:companyid");
		String code = namedParameterJdbcTemplate.queryForObject(sql.toString(), new BeanPropertySqlParameterSource(empGroup), String.class);
		empGroup.setEg_code(code);
		sql.setLength(0);
		sql.append(" INSERT INTO t_base_empgroup");
		sql.append(" (eg_code,eg_name,eg_remark,eg_shop_code,companyid)");
		sql.append(" VALUES");
		sql.append(" (:eg_code,:eg_name,:eg_remark,:eg_shop_code,:companyid)");
		KeyHolder holder = new GeneratedKeyHolder(); 
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(empGroup),holder);
		empGroup.setEg_id(holder.getKey().intValue());
		for (T_Base_EmpGroupList item : groupLists) {
			item.setEgl_eg_code(empGroup.getEg_code());
			item.setCompanyid(empGroup.getCompanyid());
		}
		sql.setLength(0);
		sql.append(" INSERT INTO t_base_empgrouplist");
		sql.append(" (egl_eg_code,egl_em_code,companyid)");
		sql.append(" VALUES");
		sql.append(" (:egl_eg_code,:egl_em_code,:companyid)");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(groupLists.toArray()));
	}
	
	@Override
	public void update(T_Base_EmpGroup empGroup, List<T_Base_EmpGroupList> groupLists) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_base_empgroup");
		sql.append(" SET eg_name=:eg_name");
		sql.append(" ,eg_remark=:eg_remark");
		sql.append(" WHERE eg_id=:eg_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(empGroup));
		
		//删除子表
		sql.setLength(0);
		sql.append(" DELETE FROM t_base_empgrouplist");
		sql.append(" WHERE egl_eg_code=:eg_code");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(), 
				new MapSqlParameterSource().addValue("eg_code", empGroup.getEg_code()).addValue("companyid", empGroup.getCompanyid()));
		//新增子表
		for (T_Base_EmpGroupList item : groupLists) {
			item.setEgl_eg_code(empGroup.getEg_code());
			item.setCompanyid(empGroup.getCompanyid());
		}
		sql.setLength(0);
		sql.append(" INSERT INTO t_base_empgrouplist");
		sql.append(" (egl_eg_code,egl_em_code,companyid)");
		sql.append(" VALUES");
		sql.append(" (:egl_eg_code,:egl_em_code,:companyid)");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(groupLists.toArray()));
	}
	
	@Override
	public void del(Integer eg_id,String eg_code,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" DELETE FROM t_base_empgroup");
		sql.append(" WHERE eg_id=:eg_id");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("eg_id", eg_id));
		sql.setLength(0);
		sql.append(" DELETE FROM t_base_empgrouplist");
		sql.append(" WHERE egl_eg_code=:eg_code");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("eg_code", eg_code).addValue("companyid", companyid));
	}
}
